<?php

/**
 * report actions.
 *
 * @package    erp-crm
 * @subpackage report
 * @author     Milwell H. Sia
 * @version    SVN: $Id: actions.class.php 12479 2008-10-31 10:54:40Z fabien $
 */
class reportActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeShowReceivables(sfWebRequest $request)
  {
    $this->customer_param = $request->getParameter('skey_contact');
    $this->report_param = $request->getParameter('skey_receivable');
    if(isset($this->report_param['detail'])){
      $this->showReceivablesDetail();
    }
  }
  
  private function showReceivablesDetail(){
    $connection = Propel::getConnection();
    $query = 
      'SELECT lt.iden2,c.iden,c.list_name,d.document_date,i.days_due,date_add(d.document_date, interval i.days_due day) due_date,'.
      'sum(lt.debit-lt.credit) AS total '.
      'FROM document d '.
      'join log_transaction lt '.
      'on d.document_iden=lt.document_iden '.
      'and d.void=\'N\' '.
      'and lt.transaction_type=\'INVOICE\' '.
      'join invoice i '.
      'on lt.iden2=i.invoice_iden '.
      'and lt.type2=\'INVOICE\' '.
      'join document d2 '.
      'on i.invoice_iden=d2.document_iden '.
      'and d2.void=\'N\' '.
      'join contact c '.
      'on c.iden=i.customer_iden ';
    if($this->report_param['report_type'] == 'as of'){
      $asof_date = $this->report_param['date_from']['year'].'-'.$this->report_param['date_from']['month'].'-'.$this->report_param['date_from']['day'];
      $query .= 'where d.document_date<=\''.date('Y-m-d',strtotime($asof_date)).'\' '.
        'and lt.entry_date<\''.date('Y-m-d',strtotime($asof_date." + 1 day")).'\' ';
    }elseif($this->report_param['report_type'] == 'date range'){
      $date_from = $this->report_param['date_from']['year'].'-'.$this->report_param['date_from']['month'].'-'.$this->report_param['date_from']['day'];
      $date_to = $this->report_param['date_to']['year'].'-'.$this->report_param['date_to']['month'].'-'.$this->report_param['date_to']['day'];
      $query .= 'where d.document_date>=\''.date('Y-m-d',strtotime($date_from)).'\' '.
        'and d.document_date<=\''.date('Y-m-d',strtotime($date_to)).'\' '.
        'and lt.entry_date<\''.date('Y-m-d',strtotime($date_to." + 1 day")).'\' ';
    }
    $query .= 'group by c.iden,lt.iden2 ';
    $query .= 'having total<>0 ';
    $query .='order by c.list_name,d.document_date';
    $statement = $connection->prepare($query);
    $statement->execute();
    $this->receivables = $statement->fetchAll(PDO::FETCH_ASSOC);
    $this->setLayout('nomenu');
  }
  
  public function executeReceivables(sfWebRequest $request){
    $this->myContactSearchForm = new myContactSearchForm();
    $this->myContactSearchForm->setCustomerMode();
    $this->myReceivableSearchForm = new myReceivableSearchForm();
  }
  
  public function executeStatementOfAccount(sfWebRequest $request){
    $customer_iden = $request->getParameter('customer_iden');
    $this->accountCustomer = AccountCustomerPeer::retrieveByPk($customer_iden);
    $this->contact = $this->accountCustomer->getContact();
    $con = Propel::getConnection();
    
    $this->receivables = myQuery::getReceivables($con,$customer_iden);

    $this->cash_advances = myQuery::getCashAdvances($con,$customer_iden);
    
    $this->check_advances = myQuery::getCheckAdvances($con,$customer_iden);

 
    $this->setLayout('nomenu');

  }
  
  
}
